unit uMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxControls, cxContainer, cxEdit, cxLabel, cxTextEdit, cxMaskEdit,
  cxDropDownEdit, cxCalendar, cxGroupBox, StdCtrls, cxButtons, cxMemo,
  cxGraphics, cxLookAndFeels, cxLookAndFeelPainters, dxSkinsCore, Menus,
  dxSkinsForm, dxSkinOffice2010Blue, DB, MemDS, DBAccess, Uni, UniProvider,
  SQLServerUniProvider, cxStyles, dxSkinscxPCPainter, cxCustomData,
  cxFilter, cxData, cxDataStorage, cxDBData, cxGridLevel, cxClasses,
  cxGridCustomView, cxGridCustomTableView, cxGridTableView,
  cxGridDBTableView, cxGrid, dxmdaset;

type
  TForm1 = class(TForm)
    cxGroupBox1: TcxGroupBox;
    cxGroupBox2: TcxGroupBox;
    edtStartDate: TcxDateEdit;
    edtEndDate: TcxDateEdit;
    cxLabel1: TcxLabel;
    cxLabel2: TcxLabel;
    btnExcute: TcxButton;
    dxSkinController1: TdxSkinController;
    SQLServerUniProvider1: TSQLServerUniProvider;
    conHis: TUniConnection;
    qrMaster: TUniQuery;
    cxGroupBox3: TcxGroupBox;
    cxGroupBox4: TcxGroupBox;
    cxGroupBox5: TcxGroupBox;
    cxGroupBox6: TcxGroupBox;
    mmoName: TcxMemo;
    cxGroupBox7: TcxGroupBox;
    cxGroupBox8: TcxGroupBox;
    mmoDrc: TcxMemo;
    tvData: TcxGridDBTableView;
    glData: TcxGridLevel;
    cgData: TcxGrid;
    qrData: TdxMemData;
    dsData: TDataSource;
    qrDataName: TStringField;
    qrDataMoney: TFloatField;
    tvDataRecId: TcxGridDBColumn;
    tvDataName: TcxGridDBColumn;
    tvDataMoney: TcxGridDBColumn;
    procedure FormCreate(Sender: TObject);
    procedure btnExcuteClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}
function FillZero(S: string; i: integer; CChar: Char = '0'; Front: Boolean = True): string;
begin
  if Front then
  begin
    while Length(S) < i do S := CChar + S;
  end
  else
  begin
    while Length(S) < i do S := S + CChar;
  end;
  Result := S;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  iYear, iMonth, iDay: Word;
begin
  DecodeDate(IncMonth(Date, -1), iYear, iMonth, iDay);
  edtStartDate.Date := EncodeDate(iYear, iMonth, 1);
  edtEndDate.Date := EncodeDate(iYear, iMonth + 1, 1) + 6;
end;

procedure TForm1.btnExcuteClick(Sender: TObject);
var
  i: Integer;
begin
  if conHis.Connected then
    conHis.Disconnect;
  conHis.Server := '192.168.2.2';
  conHis.Username := 'whxhyy';
  conHis.Password := '8120120';

  conHis.Connect;
  try
    qrData.Close;
    qrData.Open;
    for i := 0 to mmoName.Lines.Count - 1 do
    begin
      if qrMaster.Active then
        qrMaster.Close;
      qrMaster.SQL.Clear;
      qrMaster.SQL.Add('select SUM(zf11) from zhoufei');
      qrMaster.SQL.Add(' where zf10 between :sdate and :edate');
      qrMaster.SQL.Add(' and zf2=' + QuotedStr(FillZero(Trim(mmoName.Lines.Strings[i]), 10, ' ', False)) + ' and zf12=0');
      qrMaster.SQL.Add(' and zf7=' + QuotedStr(FillZero(Trim(mmoDrc.Lines.Strings[i]), 10, ' ', False)));
      qrMaster.ParamByName('sdate').AsDateTime := edtStartDate.Date;
      qrMaster.ParamByName('edate').AsDateTime := edtEndDate.Date;
      qrMaster.Open;
      qrData.Append;
      qrDataName.AsString := Trim(mmoName.Lines.Strings[i]);
      qrDataMoney.AsFloat := qrMaster.Fields[0].AsFloat;
      qrData.Post;
    end;
  finally
    conHis.Disconnect;
  end;
end;

end.
